package com.example.day9;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {
Button but1,but2,but3,but4,but5,but6,but7,but8,but9,but10;
TextView text;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        but1=findViewById(R.id.button1);
        but2=findViewById(R.id.button2);
        but3=findViewById(R.id.button3);
        but4=findViewById(R.id.button4);
        but5=findViewById(R.id.button5);
        but6=findViewById(R.id.button6);
        but7=findViewById(R.id.button7);
        but8=findViewById(R.id.button8);
        but9=findViewById(R.id.button9);
        but10=findViewById(R.id.button10);
        text=findViewById(R.id.text1);

        but1.setOnClickListener(this);
        but2.setOnClickListener(this);
        but3.setOnClickListener(this);
        but4.setOnClickListener(this);
        but5.setOnClickListener(this);
        but6.setOnClickListener(this);
        but7.setOnClickListener(this);
        but8.setOnClickListener(this);
        but9.setOnClickListener(this);
        but10.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        switch(v.getId()){
            case R.id.button1:
                Log.e("#######3","33333333");
//                new MydataBaseHelp(MainActivity.this,"day9",null,1);
                break;
            case R.id.button2://增1
                MydataBaseHelp dbOpenHelper=new MydataBaseHelp(MainActivity.this,"day9",null,1);
                SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

                ContentValues values = new ContentValues();
                values.put("Name","红孩儿");
                values.put("Age","1800");

                text.setText(db.insert("user",null,values)+"");
                //参数一表名 参数二：默认值  参数三：要插入的值
                break;
            case R.id.button3://增2
                MydataBaseHelp dbOpenHelper3=new MydataBaseHelp(MainActivity.this,"day9",null,2);
                SQLiteDatabase db3 = dbOpenHelper3.getWritableDatabase();
                db3.execSQL("insert into User(Name,sex,Age) values('风清扬','男',108)");
                break;
            case R.id.button4://改1
                MydataBaseHelp dbOpenHelper4=new MydataBaseHelp(MainActivity.this,"day9",null,2);
                SQLiteDatabase db4 = dbOpenHelper4.getWritableDatabase();
                db4.execSQL("update User set Age = 3 where name=?",new String[]{"红孩儿"});
               break;
            case R.id.button5://改2
                MydataBaseHelp dbOpenHelper5=new MydataBaseHelp(MainActivity.this,"day9",null,2);
                SQLiteDatabase db5 = dbOpenHelper5.getWritableDatabase();
                ContentValues values5 = new ContentValues();
                values5.put("Age","30");
                int  num=db5.update("User",values5,"name=?",new String[]{"风清扬"});
                text.setText(num+"");
                break;
            case R.id.button6://删1
                MydataBaseHelp dbOpenHelper6=new MydataBaseHelp(MainActivity.this,"day9",null,2);
                SQLiteDatabase db6 = dbOpenHelper6.getWritableDatabase();
                //参数一：表名  参数二：要修改的值  参数三：条件 参数四：为条件中的？赋值
                int  num2=db6.delete("User","name=?",new String[]{"红孩儿"});

                    text.setText("删除"+num2+"");
                break;
            case R.id.button7://删2
                MydataBaseHelp dbOpenHelper7=new MydataBaseHelp(MainActivity.this,"day9",null,2);
                SQLiteDatabase db7 = dbOpenHelper7.getWritableDatabase();
                String sql="delete from User where name = ? ";
                db7.execSQL(sql,new String[]{"红孩儿"});
                break;
            case R.id.button8://查1
                MydataBaseHelp dbOpenHelper8=new MydataBaseHelp(MainActivity.this,"day9",null,2);
                SQLiteDatabase db8=dbOpenHelper8.getWritableDatabase();
                //创建集合
                ArrayList<Map<String,Object>> list = new ArrayList<>();
                //执行查询语句
                Cursor cursor = db8.query("user", null, null, null, null, null, null);

//                table：表名。相当于select语句from关键字后面的部分。如果是多表联合查询，可以用逗号将两个表名分开。
//                columns：要查询出来的列名。相当于select语句select关键字后面的部分。
//                selection：查询条件子句，相当于select语句where关键字后面的部分，在条件子句允许使用占位符“?”
//                selectionArgs：对应于selection语句中占位符的值，值在数组中的位置与占位符在语句中的位置必须一致，否则就会有异常。
//                groupBy：相当于select语句group by关键字后面的部分
//                having：相当于select语句having关键字后面的部分
//                orderBy：相当于select语句order by关键字后面的部分，如：personid desc, age asc;
//                limit：指定偏移量和获取的记录数，相当于select语句limit关键字后面的部分。
                //遍历游标
                text.setText(cursor.getCount()+"");
                while (cursor.moveToNext()){

                    int id=cursor.getInt(cursor.getColumnIndex("_id"));
                    String name=cursor.getString(cursor.getColumnIndex("Name"));
                    int age=cursor.getInt(cursor.getColumnIndex("Age"));
                    String sex =cursor.getString(cursor.getColumnIndex("sex"));
                    //创建对象存入集合中
                    Log.e("###",cursor.getPosition()+"  ,"+name);
                    Map<String,Object> map = new HashMap();
                    map.put("_id",id);
                    map.put("name",name);
                    map.put("age",age);
                    map.put("sex",sex);
                    list.add(map);
                }
                text.setText(list.size()+"");
//                Cursor cursor2= db8.execSQL (String sql, String[] selectionArgs);
                break;
            case R.id.button9://删除数据库
                MydataBaseHelp dbOpenHelper9=new MydataBaseHelp(MainActivity.this,"day9",null,2);
                SQLiteDatabase db9=dbOpenHelper9.getWritableDatabase();
                db9.execSQL("delete from user");
                break;
            case R.id.button10://事务
                MydataBaseHelp dbOpenHelper10=new MydataBaseHelp(MainActivity.this,"day9",null,2);
                SQLiteDatabase db10=dbOpenHelper10.getWritableDatabase();

                db10.beginTransaction();//开启事务
                try {
                    for (int i=0;i<1000;i++) {
                        Log.e("###",i+"");
                        db10.execSQL("insert into User(Name,sex,Age) values('"+i+"风清扬','男',108)");
                    }
                    db10.setTransactionSuccessful();//成功

                }catch (Exception e){
                    e.printStackTrace();
                }finally {
                    db10.endTransaction();//结束事务
                    db10.close();//关闭
                }
                break;

        }
    }
}
